"use strict";
/**
 *
 * @author wangchenzhao
 * @date 2018/11/20
 */
const Service = require('egg').Service;

class template1 extends Service {

    constructor(param) {
        super(param);
        this.mysql = this.app.database.exam_mysql;
    }
    async getAllDataBses() {
        const { mysql } = this;
        //let data = await  mysql.query(sql,[]);
        const sql = `show databases`;
        const result = await mysql.query(sql);
        return result;
    }
    async getTablesByDataBase(db_name) {
        const { mysql } = this;
        //let data = await  mysql.query(sql,[]);
        const sql = `select table_schema, table_name,table_comment from information_schema.tables where table_schema='${db_name}' and table_type='base table'`;

        const result = await mysql.query(sql);
        return result;
    }
    async getDetailByTabAndDb(param) {
        const { mysql } = this;
        //let data = await  mysql.query(sql,[]);
        let sql = `SELECT
                        column_name,
                        column_type,
                        data_type,
                        is_nullable,
                        column_comment
                        FROM
                        INFORMATION_SCHEMA.COLUMNS c
                        where
                        c.table_schema ='${param.db_name}'
                        and c.table_name = '${param.tab_name}'`;

        const result = await mysql.query(sql);
        sql = `show create table ${param.db_name}.${param.tab_name}`;
        const createTableDDLRet = await mysql.query(sql);

        return { table_columns: result, ddl: createTableDDLRet[0]['Create Table'] };
    }
    async getDetailByTabNames(param) {
        const { mysql } = this;
        let tableArr = []
        for (const tab_name of param.tab_names) {
            const sql = `SELECT
                            column_name,
                            column_type,
                            data_type,
                            is_nullable,
                            column_comment
                            FROM
                            INFORMATION_SCHEMA.COLUMNS c
                            where
                            c.table_schema ='${param.db_name}'
                            and c.table_name = '${tab_name}'`;

            const result = await mysql.query(sql);
            tableArr.push({ tab_name: tab_name, data: result })
        }
        return tableArr;
    }
    async getTablesData(param) {
        const { mysql } = this;
        let tableArr = []
        for (const tab_name of param.tab_names) {
            let sql = `SELECT
                            column_name,
                            column_comment
                            FROM
                            INFORMATION_SCHEMA.COLUMNS c
                            where
                            c.table_schema ='${param.db_name}'
                            and c.table_name = '${tab_name}'`;

            const columns = await mysql.query(sql);
            const pageing = await this.paging(param.page_num);
            sql = `use ${param.db_name};
                         select * from ${'`' + tab_name + '`'} limit ${pageing.start}, ${pageing.page_size};`;

            const result = await mysql.query(sql);

            tableArr.push({ tab_name: tab_name, columns: columns, data: result[1] })
        }
        console.log(tableArr)
        return tableArr;
    }
    async getOneTableData(param) {
        const { mysql } = this;
        if (!param.db_name || !param.tab_name) return {
            tab_name: '',
            table_comment: '',
            columns: [],
            count: 0,
            page_count: 0,
            data: []
        };

        let sql = `SELECT
                            column_name,
                            column_comment
                            FROM
                            INFORMATION_SCHEMA.COLUMNS c
                            where
                            c.table_schema ='${param.db_name}'
                            and c.table_name = '${param.tab_name}'`;

        const columns = await mysql.query(sql);
        const pageing = await this.paging(param.page_num);
        sql = `use ${param.db_name};
                  select count(*) count from ${'`' + param.tab_name + '`'}`;
        const count_ret = await mysql.query(sql);
        const count = count_ret[1][0]['count'];
        sql = ` use ${param.db_name};
                    select * from ${'`' + param.tab_name + '`'} limit ${pageing.start}, ${pageing.page_size};`

        const result = await mysql.query(sql);
        sql = `
            
                    SELECT
                        table_schema,
                        table_name,
                        table_comment 
                    FROM
                        information_schema.TABLES 
                    WHERE
                        table_schema = '${param.db_name}' 
                        AND table_type = 'base table' 
                        AND table_name = '${param.tab_name}'
                    `
        const table = await mysql.query(sql);

        return {
            tab_name: param.tab_name,
            table_comment: table[0].table_comment,
            columns: columns,
            count: count,
            page_count: count === 0 ? 0 : Math.ceil(count / pageing.page_size),
            data: result[1]
        };
    }
    async paging(page_num) {
        const { mysql } = this;
        // 暂定每页20条
        const page_size = 20;
        const start = (page_num - 1) * 20;
        return { page_size, start };

    }
}

module.exports = template1;